The situation is as follows. We have a table with records that are added over time. The request is to produce a resultset that only contains values that are added latest.
Let me first show the table:
CREATE TABLE "RELATION_VALUES"
( "RELATION_ID" NUMBER,
"STARTDATE" DATE,
"VALUE" NUMBER
) ;
Insert into RELATION_VALUES (RELATION_ID,STARTDATE,VALUE) values ('1',to_date('11-FEB-2019','DD-MON-YYYY'),'80');
Insert into RELATION_VALUES (RELATION_ID,STARTDATE,VALUE) values ('2',to_date('12-FEB-2019','DD-MON-YYYY'),'60');
Insert into RELATION_VALUES (RELATION_ID,STARTDATE,VALUE) values ('2',to_date('14-FEB-2019','DD-MON-YYYY'),'70');
Insert into RELATION_VALUES (RELATION_ID,STARTDATE,VALUE) values ('1',to_date('14-FEB-2019','DD-MON-YYYY'),'50');
Insert into RELATION_VALUES (RELATION_ID,STARTDATE,VALUE) values ('2',to_date('15-FEB-2019','DD-MON-YYYY'),'80');
For relation_id =1 we see that over time the values 80, 50 are added.
Similarly, for relation_id=2, we see that over time the values 60, 70, 80 are added.
We would like to see a report that only contains the latest values. That implies we would like to see 50 and 80 only for relation_id 1 and 2 respectively.
The query to use is:
select
relation_id
, max(value) KEEP(DENSE_RANK last ORDER BY "STARTDATE" ASC) as value
from relation_values
group by relation_id;
The result is: